import datetime
import pymysql
from src.exceptions import sales_exception, warehouse_exception, shelves_exception, employee_exception, \
    common_exception, goods_exception


def date2datetime(to_date: datetime.date) -> datetime.datetime:
    """
    将日期转换为时间（凌晨零点）

    :param to_date: 日期
    :return: 日期对应时间
    """
    return datetime.datetime.fromisoformat(to_date.strftime("%Y-%m-%d"))


def select_star_get(cursor: pymysql.connections.Cursor, table: str, key: str) -> {}:
    """
    根据key查询table的信息

    :param cursor: sql执行游标
    :param table: 表名
    :param key: ??id，table的id属性
    :return: key对应的信息
    """
    table = table.lower()
    table_id = {'warehouse': 'Wid',
                'goods': 'Gid',
                'shelves': 'Sid',
                'employee': 'Eid',
                'purchases': 'Gid',
                }
    cursor.execute(
        "select * from {} where {} = '{}';".format(table, table_id[table], key))
    result = cursor.fetchone()
    attributes = get_attributes(cursor, table)
    base_info = generate_result_dict(attributes, result)
    return {**base_info}


def select_star_get_more(cursor: pymysql.connections.Cursor, table: str, key: str) -> {}:
    """
    根据key查询table的信息，可能有多条信息

    :param cursor: sql执行游标
    :param table: 表名
    :param key: ??id，table的id属性
    :return: key对应的信息
    """
    table = table.lower()
    table_id = {'warehouse': 'Wid',
                'goods': 'Gid',
                'shelves': 'Sid',
                'employee': 'Eid',
                'purchases': 'Gid',
                'phonenumbers': 'Eid'
                }
    cursor.execute(
        "select * from {} where {} = '{}';".format(table, table_id[table], key))
    return cursor.fetchall()


def select_star_get_all(cursor: pymysql.connections.Cursor, table: str) -> (()):
    """
    查询table的信息

    :param cursor: sql执行游标
    :param table: 表名
    :return: key对应的信息
    """
    table = table.lower()
    cursor.execute(
        "select * from {};".format(table))
    return cursor.fetchall()


def select_star_get_by_name(cursor: pymysql.connections.Cursor, table: str, key: str) -> (()):
    """
    根据名称进行查询信息

    :param cursor: 游标
    :return: key对应的信息，二维元组
    """
    table = table.lower()
    table_id = {'warehouse': 'Wname',
                'shelves': 'Sname',
                'goods': 'Gname',
                'employee': 'Ename',
                'employee_manage_view': 'Ename'
                }
    table_key = table_id[table]
    cursor.execute(
        "select * from {} where {} like '{}%' ;".format(table, table_key, key))
    return cursor.fetchall()


def select_star_get_duration(cursor: pymysql.connections.Cursor, table: str, start_date, end_date) -> (()):
    """
    根据查询table的信息，且这些信息在start_date到end_date之间

    :param cursor: sql执行游标
    :param table: 表名
    :param start_date: 开始日期
    :param end_date: 结束日期
    :return: key对应的信息，二维元组
    """
    table = table.lower()
    table_id = {'purchases': 'Purchase_date',
                'sales': 'Stime',
                'manages': 'Appointment_date',
                'launches_off_on': 'Launch_date',
                'launches_off_off': 'Off_shelf_date',
                'purchase_history_view': 'Purchase_date'
                }
    key = table_id[table]
    if table in ['launches_off_on', 'launches_off_off']:
        table = 'launches_off'
    cursor.execute(
        "select * from {} where {} between '{}' and '{}' order by {} desc ;".format(table, key, start_date,
                                                                                end_date, key))
    return cursor.fetchall()


def update_star(cursor: pymysql.connections.Cursor, table: str, changed_values: [], primary_key_value) -> (
        ()):
    """
    根据查询table的信息，且这些信息在start_date到end_date之间

    :param cursor: sql执行游标
    :param table: 表名
    :param changed_values: 需要修改的属性列表
    :param primary_key_value: 主键值
    :return:
    """
    attributes = get_attributes(cursor, table)
    if len(attributes) != len(changed_values):
        raise Exception("属性个数不匹配！")
    table = table.lower()
    table_id = {'warehouse': 'Wid',
                'shelves': 'Sid',
                'goods': 'Gid',
                'employee': 'Eid',
                }
    table_key = table_id[table]
    update_statement = ""
    for i, value in enumerate(changed_values):
        end = '' if i == len(changed_values) - 1 else ','
        if type(value) == int or type(value) == float:
            update_statement += "{}={}{} ".format(attributes[i], value, end)
        else:
            update_statement += "{}='{}'{} ".format(attributes[i], value, end)
    cursor.execute(
        "update {} set {} where {}='{}' ;".format(table, update_statement, table_key, primary_key_value))


def employee_update(cursor: pymysql.connections.Cursor, changed_values: [], phonenumbers: [], Eid) -> {}:
    """
    根据Eid修改某员工信息

    :param cursor: sql执行游标
    :param changed_values: 改变后的信息列表
    :param phonenumbers: 电话号码列表
    :param Eid: 员工号
    :return:
    """
    attributes = get_attributes(cursor, 'employee')
    if len(attributes) != len(changed_values):
        raise Exception("属性个数不匹配！")
    update_statement = ""
    for i, value in enumerate(changed_values):
        end = '' if i == len(changed_values) - 1 else ','
        if type(value) == int or type(value) == float:
            update_statement += "{}={}{} ".format(attributes[i], value, end)
        else:
            update_statement += "{}='{}'{} ".format(attributes[i], value, end)
    start_transaction(cursor)
    cursor.execute(
        "update employee set {} where Eid='{}' ;".format(update_statement, Eid))
    cursor.execute("delete from phonenumbers where Eid = '{}';".format(Eid))
    for Phonenumber in phonenumbers:
        try:
            cursor.execute("insert into phonenumbers values ('{}','{}');".format(Eid, Phonenumber))
        except pymysql.err.IntegrityError:
            rollback_transaction(cursor)
            raise common_exception.DuplicatePhoneNumber('电话号码[{}]重复'.format(Phonenumber))
        except pymysql.err.OperationalError:
            rollback_transaction(cursor)
            raise common_exception.ErrorPhoneNumber()
    commit_transaction(cursor)


def delete_star(cursor: pymysql.connections.Cursor, table: str, key: str) -> {}:
    """
    根据key删除table的某条信息

    :param cursor: sql执行游标
    :param table: 表名
    :param key: ??id，table的id属性
    :return:
    """
    table = table.lower()
    table_id = {'warehouse': 'Wid',
                'goods': 'Gid',
                'shelves': 'Sid',
                'purchases': 'Gid',
                }
    try:
        cursor.execute("delete from {} where {} = '{}';".format(table, table_id[table], key))
    except pymysql.err.IntegrityError:
        raise common_exception.DeleteError()


def employee_delete(cursor: pymysql.connections.Cursor, Eid: str) -> {}:
    """
    根据Eid删除某员工

    :param cursor: sql执行游标
    :param Eid: 员工号
    :return:
    """
    start_transaction(cursor)
    cursor.execute("delete from phonenumbers where Eid = '{}';".format(Eid))
    try:
        cursor.execute("delete from employee where Eid = '{}';".format(Eid))
    except pymysql.err.IntegrityError:
        raise common_exception.DeleteError()
    commit_transaction(cursor)


def get_earliest_date(cursor: pymysql.connections.Cursor, table: str) -> datetime.datetime:
    """
    查询最早时间，以作为查询历史记录的默认开始时间

    :param cursor: 游标
    :param table: 表名
    :return: 查询最早时间
    """
    table = table.lower()
    table_id = {'purchases': 'Purchase_date',
                'sales': 'Stime',
                'manages': 'Appointment_date',
                'launches_off': 'Launch_date',
                'purchase_history_view': 'Purchase_date'
                }
    cursor.execute(
        "select min({}) from {};".format(table_id[table], table))
    for x in cursor.fetchone():
        return date2datetime(x)


def warehouse_add(cursor: pymysql.connections.Cursor, Wname: str, Wid: str, Wdistrict: str, Wstreet: str, Wnum: int):
    """
    增加一个仓库信息

    :param cursor: sql执行游标
    :param Wname: 仓库名称
    :param Wid: 仓库号
    :param Wdistrict: 仓库所在区
    :param Wstreet: 仓库所在街道号
    :param Wnum: 仓库门牌号
    :return:
    """
    try:
        cursor.execute(
            "insert into warehouse values ('{}','{}','{}','{}',{});".format(Wname, Wid, Wdistrict, Wstreet, Wnum))
    except pymysql.err.IntegrityError:
        raise warehouse_exception.DuplicateWid()
    except pymysql.err.OperationalError:
        raise warehouse_exception.ErrorWid()


def shelves_add(cursor: pymysql.connections.Cursor, Sname: str, Sid: str, Sdistrict: str, Snum: int):
    """
    增加一个货架信息

    :param cursor: sql执行游标
    :param Sname: 货架名称
    :param Sid: 货架号
    :param Sdistrict: 货架所在区
    :param Snum: 货架所在区的具体编号
    :return:
    """
    try:
        cursor.execute(
            "insert into shelves values ('{}','{}','{}', {});".format(Sname, Sid, Sdistrict, Snum))
    except pymysql.err.IntegrityError:
        raise shelves_exception.DuplicateSid()
    except pymysql.err.OperationalError:
        raise shelves_exception.ErrorSid()


def employee_add(cursor: pymysql.connections.Cursor, Ename: str, Eid: str, Esex: str, Birthday: str, Phonenumbers: []):
    """
    增加一个员工信息

    :param cursor: sql执行游标
    :param Ename: 员工名字
    :param Eid: 员工id
    :param Esex: 性别
    :param Birthday: 出生日期，格式为YYYY-MM-DD
    :param Phonenumbers: 电话号码
    :return:
    """
    start_transaction(cursor)
    try:
        cursor.execute(
            "insert into employee values ('{}','{}','{}','{}');".format(Ename, Eid, Esex, Birthday))
    except pymysql.err.IntegrityError:
        rollback_transaction(cursor)
        raise employee_exception.DuplicateEid()
    except pymysql.err.OperationalError:
        rollback_transaction(cursor)
        raise employee_exception.ErrorEid()
    for Phonenumber in Phonenumbers:
        try:
            cursor.execute("insert into phonenumbers values ('{}','{}');".format(Eid, Phonenumber))
        except pymysql.err.IntegrityError:
            rollback_transaction(cursor)
            raise common_exception.DuplicatePhoneNumber('电话号码[{}]重复'.format(Phonenumber))
    commit_transaction(cursor)


def purchases_add(cursor: pymysql.connections.Cursor, Gname: str, Gid: str, Manufacture_date: str, Quality_days: int,
                  Manufacturer: str, Unit: str, Sprice: float, Wid: str, Su_name: str, Su_sex: str, Su_phonenumber: str,
                  Purchase_date: str, Purchase_price: float, Pnumber: int):
    """

    增加一条进货记录

    :param cursor: sql执行游标
    :param Gname: 商品名称
    :param Gid: 商品号
    :param Manufacture_date: 生产日期
    :param Quality_days: 保质期
    :param Manufacturer: 生产商名称
    :param Unit: 单位
    :param Sprice: 单位售价
    :param Wid: 仓库号
    :param Su_name: 供货商名字
    :param Su_sex: 供货商性别
    :param Su_phonenumber: 供货商电话号码
    :param Purchase_date: 进货日期
    :param Purchase_price: 单位进价
    :param Pnumber: 进货数量
    :return:
    """
    start_transaction(cursor)
    try:
        cursor.execute(
            "insert into goods values ('{}','{}','{}',{}, '{}','{}', '{}', {});".format(Gname, Gid, Manufacture_date,
                                                                                        Quality_days, Manufacturer,
                                                                                        Pnumber,
                                                                                        Unit, Sprice))
    except pymysql.err.IntegrityError:
        rollback_transaction(cursor)
        raise goods_exception.DuplicateGid()
    try:
        cursor.execute(
            "insert into purchases values ('{}','{}','{}',{}, {});".format(Wid, Gid, Purchase_date, Purchase_price,
                                                                           Pnumber))
    except pymysql.err.OperationalError:
        rollback_transaction(cursor)
        raise warehouse_exception.ErrorWid()
    try:
        cursor.execute(
            "insert into supplier values ('{}','{}','{}','{}');".format(Su_name, Gid, Su_sex, Su_phonenumber))
    except pymysql.err.OperationalError:
        rollback_transaction(cursor)
        raise common_exception.ErrorPhoneNumber()
    commit_transaction(cursor)


def launches_off_add(cursor: pymysql.connections.Cursor, Wid: str, Gid: str, Sid: str, Launch_date: str,
                     Off_shelf_date: str, Change_num: int):
    """

    增加一条上架或者下架记录，注意：一种商品只能在一个货架上进行上架，以免商品种类杂乱！

    :param cursor: sql执行游标
    :param Gid: 商品号
    :param Wid: 仓库号
    :param Sid: 货架号
    :param Launch_date: 上架日期
    :param Off_shelf_date: 下架日期
    :param Change_num: 改变数量，需要大于0
    :return:
    """
    if Launch_date is None and Off_shelf_date is None:
        raise Exception('上下架日期不能同时为空！')
    if Launch_date is not None and Off_shelf_date is not None:
        raise Exception('上下架日期同时存在！')
    if Launch_date is None:
        # 下架
        try:
            cursor.execute(
                "insert into launches_off values (null, '{}','{}','{}', null,'{}', {});".format(Sid, Gid, Wid,
                                                                                                Off_shelf_date,
                                                                                                -Change_num))
        except pymysql.err.OperationalError:
            raise warehouse_exception.NotFindWid()
        except pymysql.err.IntegrityError:
            raise warehouse_exception.NotFindWid()
    if Off_shelf_date is None:
        # 上架
        try:
            cursor.execute(
                "insert into launches_off values (null, '{}','{}','{}','{}', null, {});".format(Sid, Gid, Wid,
                                                                                                Launch_date,
                                                                                                Change_num))
        except pymysql.err.OperationalError:
            raise shelves_exception.NotFindSid()
        except pymysql.err.IntegrityError:
            raise shelves_exception.NotFindSid()


def sales_add(cursor: pymysql.connections.Cursor, Gid: str, Eid: str, Stime: str, Srecord_num: int, Sprice: float,
              Pay_account: str, Pay_mode: str):
    """
    增加售货记录

    :param cursor: sql执行游标
    :param Gid: 商品号
    :param Eid: 员工号
    :param Stime: 出售时间
    :param Srecord_num: 出售数目，需要大于0
    :param Sprice: 出售价格
    :param Pay_account: 支付账号
    :param Pay_mode: 支付模式
    :return:
    """
    Sid, num, _ = get_goods_left_shelves(cursor, Gid)
    if Srecord_num > num:
        raise sales_exception.SalesMoreThanHas("售出的比货架上的还多！出现错误！")
    try:
        cursor.execute(
            "insert into sales values (null, '{}','{}','{}', '{}', {}, {}, '{}', '{}');".format(Sid, Gid, Eid, Stime,
                                                                                                Srecord_num,
                                                                                                Sprice, Pay_account,
                                                                                                Pay_mode))
    except pymysql.err.OperationalError:
        raise employee_exception.NotFindEid("找不到该员工!")


def manages_add(cursor: pymysql.connections.Cursor, Eid: str, Wid: str, Appointment_date: str, Departure_date: str):
    """
    增加售货记录

    :param cursor: sql执行游标
    :param Eid: 员工号
    :param Wid: 仓库号
    :param Appointment_date: 上任日期
    :param Departure_date: 离任日期
    :return:
    """
    if Appointment_date is None and Departure_date is None:
        raise Exception('上下任日期不能同时为空！')
    if Appointment_date is not None and Departure_date is not None:
        raise Exception('上下任日期同时存在！')
    if Appointment_date is not None:
        # 上任
        try:
            cursor.execute(
                "insert into Manages values (null, '{}','{}','{}', null);".format(Eid, Wid, Appointment_date))
        except pymysql.err.OperationalError:
            raise warehouse_exception.NotFindWid()
        except pymysql.err.DataError:
            raise warehouse_exception.ErrorWid()
        except pymysql.err.IntegrityError:
            raise warehouse_exception.NotFindWid()
    if Departure_date is not None:
        # 离任
        try:
            cursor.execute(
                "update Manages set Departure_date = '{}' where Eid = '{}' and Wid = '{}' and Appointment_date is not null and Departure_date is null ;".format(
                    Departure_date, Eid, Wid))
        except pymysql.err.OperationalError:
            raise warehouse_exception.NotFindWid()
        except pymysql.err.DataError:
            raise warehouse_exception.ErrorWid()
        except pymysql.err.IntegrityError:
            raise warehouse_exception.NotFindWid()


def goods_get_by_name(cursor: pymysql.connections.Cursor, Gname: str) -> {}:
    """
    通过商品名称（Gname）查询仓库中的商品信息；对应仓库信息；仓库中商品余量；货架信息；货架中商品余量
    返回结果示例：
    {
        '0000001889':{ 'Gname': 'Gname1889',
                        'Gid': '0000001889',
                       'Manufacture_date': datetime.date(2020, 4, 8),
                       'Quality_days': 1247,
                       'Manufacturer': 'Manufacturer89',
                       'Number': 262,
                       'Unit': '个',
                       'Sprice': 73.50155145035964,
                       'warehouses':
                       {
                            '0231': {
                                    'Wname': 'Wname231',
                                    'Wid': '0231',
                                    'Wdistrict': '道里区',
                                    'Wstreet': '保健路',
                                    'Wnum': 31,
                                    'left': Decimal('258')
                                    }
                        },
                        'shelves':
                        {
                            'Sname': 'Sname99',
                            'Sid': '0099',
                            'Sdistrict': '南',
                            'Snum': 99,
                            'left': Decimal('1'),
                            'latest_launch_date': datetime.date(2022, 9, 1)
                        }
                    }
    }

    :param cursor: sql执行游标
    :param Gname: 商品名称
    :return: 商品信息
    """
    goods_attributes = get_attributes(cursor, 'goods')
    cursor.execute(
        "select * from goods where Gname like '{}%';".format(Gname))
    info = {}
    for Gname, Gid, Manufacture_date, Quality_days, Manufacturer, Number, Unit, Sprice in cursor.fetchall():
        info[Gid] = generate_result_dict(goods_attributes,
                                         [Gname, Gid, Manufacture_date, Quality_days, Manufacturer, Number, Unit,
                                          Sprice])
        info[Gid]['warehouses'] = {}
        for Wid, warehouse_left in get_goods_left_warehouse_all(cursor, Gid):
            warehouse_info = select_star_get(cursor, 'warehouse', Wid)
            warehouse_info['left'] = warehouse_left
            info[Gid]['warehouses'][Wid] = warehouse_info
        info[Gid]['shelves'] = {}
        goods_left_shelves = get_goods_left_shelves(cursor, Gid)
        if goods_left_shelves is None:
            continue
        Sid, shelves_left, latest_launch_date = goods_left_shelves
        info[Gid]['shelves'] = select_star_get(cursor, 'shelves', Sid)
        info[Gid]['shelves']['left'] = shelves_left
        info[Gid]['shelves']['latest_launch_date'] = latest_launch_date
    return info


def goods_get_all(cursor: pymysql.connections.Cursor, max_num_records: int = 100) -> {}:
    """
    查询仓库中的所有的商品信息；对应仓库信息；仓库中商品余量；货架信息；货架中商品余量
    返回结果示例：
    {
        '0000001889':{ 'Gname': 'Gname1889',
                        'Gid': '0000001889',
                       'Manufacture_date': datetime.date(2020, 4, 8),
                       'Quality_days': 1247,
                       'Manufacturer': 'Manufacturer89',
                       'Number': 262,
                       'Unit': '个',
                       'Sprice': 73.50155145035964,
                       'warehouses':
                       {
                            '0231': {
                                    'Wname': 'Wname231',
                                    'Wid': '0231',
                                    'Wdistrict': '道里区',
                                    'Wstreet': '保健路',
                                    'Wnum': 31,
                                    'left': Decimal('258')
                                    }
                        },
                        'shelves':
                        {
                            'Sname': 'Sname99',
                            'Sid': '0099',
                            'Sdistrict': '南',
                            'Snum': 99,
                            'left': Decimal('1'),
                            'latest_launch_date': datetime.date(2022, 9, 1)
                        }
                    }
    }

    :param cursor: sql执行游标
    :param max_num_records: 返回最多结果条数
    :return: 商品信息
    """
    goods_attributes = get_attributes(cursor, 'goods')
    cursor.execute(
        "select * from goods limit {};".format(max_num_records))
    info = {}
    for Gname, Gid, Manufacture_date, Quality_days, Manufacturer, Number, Unit, Sprice in cursor.fetchall():
        info[Gid] = generate_result_dict(goods_attributes,
                                         [Gname, Gid, Manufacture_date, Quality_days, Manufacturer, Number, Unit,
                                          Sprice])
        info[Gid]['warehouses'] = {}
        for Wid, warehouse_left in get_goods_left_warehouse_all(cursor, Gid):
            warehouse_info = select_star_get(cursor, 'warehouse', Wid)
            warehouse_info['left'] = warehouse_left
            info[Gid]['warehouses'][Wid] = warehouse_info
        info[Gid]['shelves'] = {}
        goods_left_shelves = get_goods_left_shelves(cursor, Gid)
        if goods_left_shelves is None:
            continue
        Sid, shelves_left, latest_launch_date = goods_left_shelves
        info[Gid]['shelves'] = select_star_get(cursor, 'shelves', Sid)
        info[Gid]['shelves']['left'] = shelves_left
        info[Gid]['shelves']['latest_launch_date'] = latest_launch_date
    return info


def get_goods_about_to_expire(cursor: pymysql.connections.Cursor, now_date: str, min_left_quality_days: int) -> {}:
    """
        查询仓库中的所有的商品信息；对应仓库信息；仓库中商品余量；货架信息；货架中商品余量
        返回结果示例：
        {
            '0000001889':{ 'Gname': 'Gname1889',
                            'Gid': '0000001889',
                           'Manufacture_date': datetime.date(2020, 4, 8),
                           'Quality_days': 1247,
                           'Manufacturer': 'Manufacturer89',
                           'Number': 262,
                           'Unit': '个',
                           'Sprice': 73.50155145035964,
                           'expiration_date': datetime.date(2019, 10, 13),
                           'warehouses':
                           {
                                '0231': {
                                        'Wname': 'Wname231',
                                        'Wid': '0231',
                                        'Wdistrict': '道里区',
                                        'Wstreet': '保健路',
                                        'Wnum': 31,
                                        'left': Decimal('258')
                                        }
                            },
                            'shelves':
                            {
                                'Sname': 'Sname99',
                                'Sid': '0099',
                                'Sdistrict': '南',
                                'Snum': 99,
                                'left': Decimal('1'),
                                'latest_launch_date': datetime.date(2022, 9, 1)
                            }
                        }
        }

        :param cursor: sql执行游标
        :return: 商品信息
        """
    info = {}
    cursor.execute(
        "select Gid, date_add(Manufacture_date, interval Quality_days day) from goods where date_add(Manufacture_date, interval (Quality_days-{}) day) <= '{}'".format(
            min_left_quality_days, now_date))
    for Gid, expiration_date in cursor.fetchall():
        goods_attributes = get_attributes(cursor, 'goods')
        cursor.execute(
            "select * from goods where Gid = '{}';".format(Gid))
        Gname, Gid, Manufacture_date, Quality_days, Manufacturer, Number, Unit, Sprice = cursor.fetchone()
        info[Gid] = generate_result_dict(goods_attributes,
                                         [Gname, Gid, Manufacture_date, Quality_days, Manufacturer, Number, Unit,
                                          Sprice])
        info[Gid]['expiration_date'] = expiration_date
        info[Gid]['warehouses'] = {}
        for Wid, warehouse_left in get_goods_left_warehouse_all(cursor, Gid):
            warehouse_info = select_star_get(cursor, 'warehouse', Wid)
            warehouse_info['left'] = warehouse_left
            info[Gid]['warehouses'][Wid] = warehouse_info
        info[Gid]['shelves'] = {}
        goods_left_shelves = get_goods_left_shelves(cursor, Gid)
        if goods_left_shelves is None:
            continue
        Sid, shelves_left, latest_launch_date = goods_left_shelves
        info[Gid]['shelves'] = select_star_get(cursor, 'shelves', Sid)
        info[Gid]['shelves']['left'] = shelves_left
        info[Gid]['shelves']['latest_launch_date'] = latest_launch_date
    return info


def get_goods_left_shelves(cursor: pymysql.connections.Cursor, Gid: str):
    """
    获取商品对应的货架，以及在该货架上剩余的该商品的数目

    :param cursor: 游标
    :param Gid: 商品号
    :return: 货架号，剩余的该商品的数，最晚被上架的那一次记录的时间；如果该商品未被上架，则返回None
    """
    cursor.execute(
        "select Sid, sum(Change_num), max(Launch_date) from (goods natural join Launches_off) where Gid = '{}' group by Sid;".format(
            Gid))
    result = cursor.fetchall()
    for Sid, num, latest_launch_date in result:
        if Sid is None:
            return None
        cursor.execute(
            "select sum(Srecord_num) from sales where Gid = '{}' and Sid = '{}';".format(Gid, Sid))
        for x in cursor.fetchone():
            x = 0 if x is None else x
            return Sid, num - x, latest_launch_date


def get_goods_left_warehouse(cursor: pymysql.connections.Cursor, Gid: str, Wid: str) -> int:
    """
    获取仓库中该商品的数目

    :param cursor: 游标
    :param Gid: 商品号
    :param Wid: 仓库号
    :return:
    """
    cursor.execute(
        "select Pnumber from (goods natural join Purchases) where Gid = '{}' and Wid = '{}';".format(Gid, Wid))
    Pnumber = cursor.fetchone()  # 进货时的该仓库的进货数目
    p_number = 0
    for num in Pnumber:
        p_number = num
    cursor.execute(
        "select -sum(Change_num) from Launches_off where Gid = '{}' and Wid = '{}';".format(Gid, Wid))
    result = cursor.fetchone()
    for x in result:
        x = 0 if x is None else x
        return p_number + x


def get_goods_left_warehouse_all(cursor: pymysql.connections.Cursor, Gid: str) -> [()]:
    """
    获取该商品在各个仓库上的数目

    :param cursor: 游标
    :param Gid: 商品号
    :return: 二维数组结果，一个元素为(Wid, num)
    """
    cursor.execute(
        "select Wid, Pnumber from (goods natural join Purchases) where Gid = '{}';".format(Gid))
    Pnumbers = cursor.fetchall()  # 进货时的进货数目
    left_nums = []
    selected = set()
    for Wid, Pnumber in Pnumbers:
        selected.add(Wid)
        cursor.execute(
            "select -sum(Change_num) from Launches_off where Gid = '{}' and Wid = '{}';".format(Gid, Wid))
        result = cursor.fetchone()
        for x in result:
            x = 0 if x is None else x
            left_nums.append((Wid, Pnumber + x))
            break
    cursor.execute(
        "select Wid, -sum(Change_num) from Launches_off where Gid = '{}' group by Wid;".format(Gid))
    for Wid, change_num in cursor.fetchall():
        if Wid in selected:
            continue
        left_nums.append((Wid, change_num))
    return left_nums


def get_sales_info_by_name(cursor: pymysql.connections.Cursor, Gname: str, start_time: str,
                           end_time: str) -> (()):
    """
    根据时间段进行按商品名称（模糊查询）查询商品号,总销售额,总利润,总销量

    :param cursor: 游标
    :param Gname: 商品名称
    :param start_time: 开始时间
    :param end_time: 结束时间
    :return: 二维元组，一维中：(商品号,商品名称,总销售额,总利润,总销量)
    """
    # cursor.execute(
    #     "select Gid, "  # 商品号
    #     "Srecord_price,"
    #     "Sprice,"
    #     "Srecord_num,"
    #     "Stime "
    #     "from (goods natural join Sales) where Gname = '{}' and (Stime between '{}' and '{}');".format(
    #         Gname, start_time, end_time))
    # print(cursor.fetchall())
    cursor.execute(
        "select Gid, "  # 商品号
        "Gname, "  # 商品名称
        "sum(Srecord_price*Srecord_num) as sales_total,"  # 总销售额
        "sum((Srecord_price-Sprice)*Srecord_num) as profit,"  # 总利润
        "sum(Srecord_num) as sales_num "  # 总销量
        "from (goods natural join Sales) where Gname like '{}%' and (Stime between '{}' and '{}') group by Gid order by profit desc ;".format(
            Gname, start_time, end_time))
    return cursor.fetchall()


def get_sales_info_all(cursor: pymysql.connections.Cursor, start_time: str, end_time: str) -> ():
    """
    根据时间段进行查询所有商品总销售额,总利润,总销量

    :param cursor: 游标
    :param start_time: 开始时间
    :param end_time: 结束时间
    :return:  商品号,商品名,总销售额,总利润,总销量
    """
    cursor.execute(
        "select Gid, "  # 商品号
        "Gname, "  # 商品名
        "sum(Srecord_price*Srecord_num) as sales_total,"  # 总销售额
        "sum((Srecord_price-Sprice)*Srecord_num) as profit,"  # 总利润
        "sum(Srecord_num) as sales_num "  # 总销量
        "from (goods natural join Sales) where (Stime between '{}' and '{}') group by Gid order by profit desc;".format(
            start_time,
            end_time))
    return cursor.fetchall()


def start_transaction(cursor: pymysql.connections.Cursor):
    """
    事务开始

    :param cursor: 游标
    :return:
    """
    cursor.execute("begin;")


def commit_transaction(cursor: pymysql.connections.Cursor):
    """
    事务提交

    :param cursor: 游标
    :return:
    """
    cursor.execute("commit; ")


def rollback_transaction(cursor: pymysql.connections.Cursor):
    """
    事务回滚

    :param cursor: 游标
    :return:
    """
    cursor.execute("rollback;")


def get_attributes(cursor: pymysql.connections.Cursor, table_name: str) -> []:
    """
    通过表名返回表的属性

    :param cursor: 游标
    :param table_name: 表名
    :return: 表的属性列表
    """
    attributes = []
    cursor.execute(
        "show columns from {};".format(table_name))
    for attribute in cursor.fetchall():
        for i in attribute:
            attributes.append(i)
            break
    return attributes


def get_types(cursor: pymysql.connections.Cursor, table_name: str) -> []:
    """
    通过表名返回表的属性的类型

    :param cursor: 游标
    :param table_name: 表名
    :return: 表的属性的类型列表，主键的序号列表
    """
    cursor.execute("show columns from {};".format(table_name))
    types = []
    primaries = []
    for i, result in enumerate(cursor.fetchall()):
        _, t, _, primary, _, _ = result
        types.append(t)
        if primary == 'PRI':
            primaries.append(i)
    return types, primaries


def generate_result_dict(attributes: [], result: ()):
    """
    构造结果字典

    :param attributes: 属性列表
    :param result: 一条元组结果
    :return: 结果字典
    """
    temp = {}
    for index, r in enumerate(result):
        temp[attributes[index]] = r
    return temp
